// Created by Amit Mudgal (MicroStrategy Principal Consultant ) on 4/2/2014.
// This would be used for all SQL statement executions on Microsoft SQL Server

package com.amex.SQL;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;

import com.americanexpress.Crypto.Encryption;



public class DBOperations {


	private static final String DRIVER_NAME;
	private static final String URL;
	private static final String USER;
	private static final String PASS;
	public static String host;
	public static String port;
	public static String dbName;
	public static String url;
	
	static {
        ResourceBundle props = ResourceBundle.getBundle("DBConnection");
        Encryption ecrypt = new Encryption();
        DRIVER_NAME = props.getString("SQL_DRIVER");
        USER 		= props.getString("SQL_UID");
        PASS 		= ecrypt.Decrypt(props.getString("SQL_EPWD"));
        host 	= props.getString("SQL_HOST");
        port 	= props.getString("SQL_PORT");
        dbName 	= props.getString("SQL_DB");
        url 	= props.getString("SQL_CONNECTION_URL");
        // URL = "jdbc:netezza://" + host + ":" + port + "/" + dbName;
		URL = url + host + ":" + port + "/" + dbName;
        	
        
	}
	
	// This method is used to connect to the Database.
	public static Connection getConnection() {
		Connection conn = null;
		
		try {
			Class.forName(DRIVER_NAME);
			conn = QueryExecution.SQLconnect(host, dbName, USER, PASS, DRIVER_NAME, url, port);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		return conn;
	}
	
   
	public static void disconnect(Connection conn, Statement stat, ResultSet res) {
		disconnect(res);
		disconnect(stat);
		disconnect(conn);
	}
	public static void disconnect(Connection conn, ResultSet res) {
		disconnect(res);
		disconnect(conn);
	}
	public static void disconnect(Connection conn, Statement stat) {
		disconnect(stat);
		disconnect(conn);
	}
	
	// This method is used to disconnect from the Database.	
	public static void disconnect(Connection conn) {
		try {
			conn.close();
		} catch (Throwable e) {
		}
	}
	
	public static void disconnect(Statement stat) {
		try {
			stat.close();
		} catch (Throwable e) {
		}
	}
	
	public static void disconnect(ResultSet res) {
		try {
			res.close();
		} catch (Throwable e) {
		}
	}
	
	/**
	 * @deprecated This method has a memory leak
	 * 
	 */
	// This method is used to run queries
	public static ResultSet execSQLQuery(Connection conn, String SQL) {
		ResultSet rs = null;
		try {
		  Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
		  rs = stmt.executeQuery (SQL);
		} catch (Exception e) {System.out.println("ERROR: While running Query ");}
		return rs;
	}
	
	
	
}


